﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;

namespace MySqlNamesDb
{
    /// <summary>
    /// I was using DBLinq MySql LINQ classes, but found this too slow, so have used .NET provider for now.
    /// </summary>
    /// 
    public class TaxonNamesData : NamesDataAccess.INamesData
    {
        private static string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["names"].ConnectionString;


        /// <summary>
        /// For DBLinq LINQ name class - OBSOLETE?
        /// </summary>
        /// <param name="nameTbl"></param>
        /// <returns></returns>
        private TaxonNamesInterface.Name LoadNameFromLinq(name nameTbl)
        {
            TaxonNamesInterface.Name newName = new TaxonNamesInterface.Name();

            newName.Authors = nameTbl.Authors;
            newName.BasionymAuthors = nameTbl.BasionymAuthors;
            newName.Canonical = nameTbl.Canonical;
            newName.CombinationAuthors = nameTbl.CombinationAuthors;
            newName.Illegitimate = nameTbl.Illegitimate;
            newName.InCitation = nameTbl.InCitation;
            newName.Invalid = nameTbl.Invalid;
            newName.MicroReference = nameTbl.MicroReference;
            newName.Misapplied = nameTbl.Misapplied;
            newName.NameFull = nameTbl.NameFull;
            //todo ? newName.NameFullFormatted 
            newName.NameId = new Guid(nameTbl.NameID);
            //todo ? newName.NameLSID = nameTbl.NameLSI;
            newName.Orthography = nameTbl.Orthography;
            newName.ProParte = nameTbl.ProParte;
            newName.PublishedIn = nameTbl.PublishedIn;
            if (nameTbl.RankID != null)
            {
                newName.RankId = new Guid(nameTbl.RankID);
                newName.NameRank = RankById(newName.RankId);
            }
            if (nameTbl.ReferenceID != null) newName.ReferenceId = new Guid(nameTbl.ReferenceID);
            newName.TypeVoucher = nameTbl.TypeVoucher;
            newName.YearOfPublication = nameTbl.YearOfPublication;
            newName.YearOnPublication = nameTbl.YearOnPublication;

            if (nameTbl.BasedOnID != null) newName.BasedOnId = new Guid(nameTbl.BasedOnID);
            if (nameTbl.BasionymID != null) newName.BasionymId = new Guid(nameTbl.BasionymID);
            if (nameTbl.BlockingNameID != null) newName.BlockingNameId = new Guid(nameTbl.BlockingNameID);
            if (nameTbl.ConservedAgainstID != null) newName.ConservedAgainstId = new Guid(nameTbl.ConservedAgainstID);
            if (nameTbl.HomonymOfID != null) newName.HomonymOfId = new Guid(nameTbl.HomonymOfID);
            if (nameTbl.ParentNameID != null) newName.ParentNameId = new Guid(nameTbl.ParentNameID);
            if (nameTbl.PreferredNameID != null) newName.PreferredNameId = new Guid(nameTbl.PreferredNameID);
            if (nameTbl.ReplacementForID != null) newName.ReplacementForId = new Guid(nameTbl.ReplacementForID);
            if (nameTbl.TypeNameID != null) newName.TypeNameId = new Guid(nameTbl.TypeNameID);
            
            return newName;
        }

        private TaxonNamesInterface.Name LoadNameFromDs(DataRow row)
        {
            TaxonNamesInterface.Name newName = new TaxonNamesInterface.Name();

            newName.Authors = row["Authors"].ToString();
            newName.BasionymAuthors = row["BasionymAuthors"].ToString();
            newName.Canonical = row["Canonical"].ToString();
            newName.CombinationAuthors = row["CombinationAuthors"].ToString();
            newName.Illegitimate = (bool)row["Illegitimate"];
            newName.InCitation = (bool)row["InCitation"];
            newName.Invalid = (bool)row["Invalid"];
            newName.MicroReference = row["MicroReference"].ToString();
            newName.Misapplied = (bool)row["Misapplied"];
            newName.NameFull = row["NameFull"].ToString();
            //todo ? newName.NameFullFormatted 
            newName.NameId = new Guid(row["NameID"].ToString());
            //todo ? newName.NameLSID = nameTbl.NameLSI;
            newName.Orthography = row["Orthography"].ToString();
            newName.ProParte = (bool)row["ProParte"];
            newName.PublishedIn = row["PublishedIn"].ToString();
            if (!row.IsNull("RankID"))
            {
                newName.RankId = new Guid(row["RankID"].ToString());
                newName.NameRank = RankById(newName.RankId);
            }
            if (!row.IsNull("ReferenceID")) newName.ReferenceId = new Guid(row["ReferenceID"].ToString());
            newName.TypeVoucher = row["TypeVoucher"].ToString();
            newName.YearOfPublication = row["YearOfPublication"].ToString();
            newName.YearOnPublication = row["YearOnPublication"].ToString();

            if (!row.IsNull("BasedOnID")) newName.BasedOnId = new Guid(row["BasedOnID"].ToString());
            if (!row.IsNull("BasionymID")) newName.BasionymId = new Guid(row["BasionymID"].ToString());
            if (!row.IsNull("BlockingNameID")) newName.BlockingNameId = new Guid(row["BlockingNameID"].ToString());
            if (!row.IsNull("ConservedAgainstID")) newName.ConservedAgainstId = new Guid(row["ConservedAgainstID"].ToString());
            if (!row.IsNull("HomonymOfID")) newName.HomonymOfId = new Guid(row["HomonymOfID"].ToString());
            if (!row.IsNull("ParentNameID")) newName.ParentNameId = new Guid(row["ParentNameID"].ToString());
            if (!row.IsNull("PreferredNameID")) newName.PreferredNameId = new Guid(row["PreferredNameID"].ToString());
            if (!row.IsNull("ReplacementForID")) newName.ReplacementForId = new Guid(row["ReplacementForID"].ToString());
            if (!row.IsNull("TypeNameID")) newName.TypeNameId = new Guid(row["TypeNameID"].ToString());

            return newName;
        }

        private TaxonNamesInterface.Concept LoadConceptFromDs(DataRow row)
        {
            TaxonNamesInterface.Concept c = new TaxonNamesInterface.Concept();

            c.AccordingTo = row["AccordingTo"].ToString();
            if (!row.IsNull("AccordingToId")) c.AccordingToId = row["AccordingTo"];
            if (!row.IsNull("ChangeDate")) c.ChangeDate = row["ChangeDate"];
            if (!row.IsNull("ChangeUser")) c.ChangeUser = row["ChangeUser"];
            c.ConceptId = row["ConceptId"];
            c.ConceptLSID = row["ConceptLSID"].ToString();
            c.TaxonName = row["TaxonName"].ToString();
            c.TaxonNameId = row["TaxonNameId"];

            return c;
        }

        private TaxonNamesInterface.ConceptRelationship LoadConceptRelFromDs(DataRow row)
        {
            TaxonNamesInterface.ConceptRelationship cr = new TaxonNamesInterface.ConceptRelationship();

            cr.Concept1Id = row["Concept1Id"];
            cr.Concept2Id = row["Concept2Id"];
            cr.ConceptRelationshipId = row["ConceptRelationshipId"];
            cr.ConceptRelationshipLSID = row["ConceptRelationshipLSID"];
            if (!row.IsNull("HybridOrder")) cr.HybridOrder = row["HybridOrder"];
            if (!row.IsNull("ChangeDate")) cr.ChangeDate = row["ChangeDate"];
            if (!row.IsNull("ChangeUser")) cr.ChangeUser = row["ChangeUser"];
            cr.Relationship = row["Relationship"];
            cr.RelationshipId = row["RelationshipId"];

            return cr;
        }

        #region INamesData Members

        public void DeleteConceptRelationship(string ConceptRelLSID, string NewConceptRelLSID, string user)
        {
            throw new NotImplementedException();
        }

        public void DeleteNameAuthors(Guid nameId)
        {
            throw new NotImplementedException();
        }

        public void DeleteNameRecord(string nameLsid, string newNameLsid, string user)
        {
            throw new NotImplementedException();
        }

        public TaxonNamesInterface.Concept GetConcept(Guid conceptId)
        {
            throw new NotImplementedException();
        }

        public TaxonNamesInterface.ConceptRelationship GetConceptRelationship(Guid ConceptRelId)
        {
            throw new NotImplementedException();
        }

        public TaxonNamesInterface.Name GetName(Guid nameId)
        {
            //Old DBLinq code
            //TaxonNames db = new TaxonNames(new MySql.Data.MySqlClient.MySqlConnection(ConnectionString));
            //string idStr = nameId.ToString();

            //var x = from rows in db.name
            //    where rows.NameID == idStr
            //    select rows;

            //if (x.Count() > 0)
            //{
            //    return LoadNameFromDs(x.First());
            //}

            TaxonNamesInterface.Name n = null;

            using (OdbcConnection cnn = new OdbcConnection(ConnectionString))
            {
                OdbcCommand cmd = new OdbcCommand("select * from name where nameid = '" + nameId.ToString() + "'");
                cmd.Connection = cnn;

                OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    n = LoadNameFromDs(ds.Tables[0].Rows[0]);
                }
            }
                        
            return n;
        }

        public List<TaxonNamesInterface.NameAuthor> GetNameAuthors(Guid nameId)
        {
            throw new NotImplementedException();
        }

        public List<TaxonNamesInterface.Name> GetNameChildren(Guid nameId)
        {
            throw new NotImplementedException();
        }

        public List<TaxonNamesInterface.ConceptRelationship> GetNameConceptRelationshipRecords(Guid nameId, bool includeToConcepts)
        {
            throw new NotImplementedException();
        }

        public List<TaxonNamesInterface.Concept> GetNameConcepts(Guid nameId)
        {
            List<TaxonNamesInterface.Concept> cList = new List<TaxonNamesInterface.Concept>;

            using (OdbcConnection cnn = new OdbcConnection(ConnectionString))
            {
                OdbcCommand cmd = new OdbcCommand("select * from concept where nameid = '" + nameId.ToString() + "'");
                cmd.Connection = cnn;

                OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow r in ds.Tables[0].Rows)
                    {
                        LoadNameFromDs
                    }
                }
            }

            return cList;
        }

        public string GetNameFull(Guid nameId)
        {
            throw new NotImplementedException();
        }

        public List<string> GetNameLSIDs(int pageNumber, int pageSize)
        {
            throw new NotImplementedException();
        }

        public List<TaxonNamesInterface.Name> GetNameSynonyms(Guid nameId)
        {
            throw new NotImplementedException();
        }

        public List<TaxonNamesInterface.Rank> GetRanks()
        {
            List<TaxonNamesInterface.Rank> ranks = new List<TaxonNamesInterface.Rank>();
            
            using (OdbcConnection cnn = new OdbcConnection(ConnectionString))
            {
                OdbcCommand cmd = new OdbcCommand("select * from rank");
                cmd.Connection = cnn;

                OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        TaxonNamesInterface.Rank r = new TaxonNamesInterface.Rank();
                        r.Abbreviation = row["Abbreviation"].ToString();
                        r.Name = row["Name"].ToString();
                        r.KnownAbbreviations = row["KnownAbbreviations"].ToString();
                        r.RankId = new Guid(row["RankId"].ToString());
                        if (!row.IsNull("Sort")) r.Sort = (int)row["Sort"];
                        r.TCSRank = row["TCSRank"].ToString();
                        r.ChangeDate = (DateTime)row["ChangeDate"];
                        r.ChangeUser = row["ChangeUser"].ToString();

                        ranks.Add(r);
                    }
                }
            }

            return ranks;
        }

        public List<TaxonNamesInterface.RelationshipType> GetRelationshipTypes()
        {
            throw new NotImplementedException();
        }

        public void InsertUpdateFieldStatus(string recordId, int fieldStatusId, int statusLevelFk, string user)
        {
            throw new NotImplementedException();
        }

        public void InsertUpdateName(TaxonNamesInterface.Name n, string user)
        {
            throw new NotImplementedException();
        }

        public TaxonNamesInterface.NameAuthor InsertUpdateNameAuthor(int nameAuthorId, Guid nameId, string basAuthors, string combAuthors, string basExAuth, string combExAuth, bool isCorrected, string user)
        {
            throw new NotImplementedException();
        }

        public TaxonNamesInterface.Rank RankById(Guid id)
        {
            TaxonNamesInterface.Rank r = null;

            //old DBLinq code
            //string idStr = id.ToString();

            //TaxonNames db = new TaxonNames(new MySql.Data.MySqlClient.MySqlConnection(ConnectionString));
            //var x = from rows in db.rank
            //        where rows.RankID == idStr
            //        select rows;

            //if (x.Count() > 0)
            //{
            //    rank qr = x.First();
            //    r = new TaxonNamesInterface.Rank();
            //    r.Abbreviation = qr.Abbreviation;
            //    r.Name = qr.Name;
            //    r.KnownAbbreviations = qr.KnownAbbreviations;
            //    r.RankId = id;
            //    r.Sort = qr.Sort;
            //    r.TCSRank = qr.TcsrAnk;
            //    r.ChangeDate = qr.ChangeDate;
            //    r.ChangeUser = qr.ChangeUser;
            //}

            using (OdbcConnection cnn = new OdbcConnection(ConnectionString))
            {
                OdbcCommand cmd = new OdbcCommand("select * from rank where rankid = '" + id.ToString() + "'");
                cmd.Connection = cnn;

                OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    DataRow row = ds.Tables[0].Rows[0];
                    r = new TaxonNamesInterface.Rank();
                    r.Abbreviation = row["Abbreviation"].ToString();
                    r.Name = row["Name"].ToString();
                    r.KnownAbbreviations = row["KnownAbbreviations"].ToString();
                    r.RankId = id;
                    if (!row.IsNull("Sort")) r.Sort = (int)row["Sort"];
                    r.TCSRank = row["TCSRank"].ToString();
                    r.ChangeDate = (DateTime)row["ChangeDate"];
                    r.ChangeUser = row["ChangeUser"].ToString();
                }
            }

            return r;
        }

        public TaxonNamesInterface.Rank RankByName(string name)
        {
            throw new NotImplementedException();
        }

        public List<TaxonNamesInterface.Name> SearchNames(string searchText, bool anywhereInText)
        {
            List<TaxonNamesInterface.Name> names = new List<TaxonNamesInterface.Name>();

            //old dblinq code
            //TaxonNames db = new TaxonNames(new MySql.Data.MySqlClient.MySqlConnection(ConnectionString));
            //if (anywhereInText)
            //{
            //    var x = from rows in db.name
            //            where rows.NameFull.IndexOf(searchText) != -1
            //            select rows;

            //    foreach (var r in x)
            //    {
            //        names.Add(LoadNameFromLinq(r));
            //    }
            //}
            //else
            //{
            //    var x = from rows in db.name
            //            where rows.NameFull.StartsWith(searchText)
            //            select rows;

            //    foreach (var r in x)
            //    {
            //        names.Add(LoadNameFromLinq(r));
            //    }
            //}


            using (OdbcConnection cnn = new OdbcConnection(ConnectionString))
            {
                OdbcCommand cmd = new OdbcCommand();
                if (anywhereInText)
                {
                    cmd.CommandText = "select * from name where namefull like '%" + searchText + "%'";
                }
                else
                {
                    cmd.CommandText = "select * from name where namefull like '" + searchText + "%'";
                }

                cmd.Connection = cnn;

                OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        TaxonNamesInterface.Name n = LoadNameFromDs(row);
                        names.Add(n);
                    }
                }
            }

            return names;
        }

        public void UpdateConcept(TaxonNamesInterface.Concept conc, string user)
        {
            throw new NotImplementedException();
        }

        public void UpdateConceptRelationship(TaxonNamesInterface.ConceptRelationship cr, string user)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
